package com.jysz.certificate.persistence.dao.mf;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.jysz.certificate.persistence.dao.AbstractBaseDao;
import com.jysz.certificate.persistence.model.mf.BillApply;
import com.jysz.certificate.persistence.model.mf.BillOption;
import com.jysz.certificate.persistence.model.system.Financial;

/**
 * 发票申请DAO
 * @author xuxiaolong
 */
@Repository
public class BillApplyDao extends AbstractBaseDao<BillApply, Long> {

	public List<Object[]> view(Long applyId){

		List<Financial> fins=getFins(applyId);
		StringBuilder sb = new StringBuilder(
				"SELECT BILLID AS BILLID,myshop._shop_mark AS shopMark,myshop._shuo_name AS shopName ,");
		if(fins!=null){
			for(Financial fin:fins)
				sb.append(" SUM(CASE WHEN BANK='").append(fin.getId()).append("' THEN _amount ELSE 0 END) AS '").append(fin.getName()).append("',");
		}
//		sb.append(" SUM(CASE WHEN BANK='1' THEN _amount ELSE 0 END) AS 中行,");
//		sb.append("	SUM(CASE WHEN BANK='4' THEN _amount ELSE 0 END) AS 工行,");
		sb.append("	COUNTS AS 总计 ,ALONEMONTH AS isAloneMonth,ALONE AS  isAlone");
		sb.append("	FROM (");
		sb.append("	SELECT bill._shop_id AS SHOP,bill.id AS BILLID,opt._financial_id AS BANK,opt._amount,bill._receivable_amount COUNTS,bill.aloneMonth as ALONEMONTH,bill.aloneOpen AS ALONE FROM mf_bill_ bill LEFT JOIN rs_mf_bill_option_ rs  ON bill.id=rs._bill_id  LEFT JOIN mf_bill_option_ opt ON opt.id=rs._option_id WHERE  ");
		sb.append("	 bill._bill_apply_id='").append(applyId).append("' ");
		sb.append("	) AS rus,sys_shop_ AS myshop WHERE rus.SHOP=myshop.id ");
		sb.append(" GROUP BY SHOP ORDER BY myshop._shop_mark");
		

		SQLQuery sqlQuery = getSession().createSQLQuery(sb.toString());
//		sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		 List<Object[]> list=sqlQuery.list();
		 for(Object[] objs:list){
			 for(Object obj:objs)
				 System.out.println("总共长度:"+objs.length);
		 }
	
		return list;
	}
	public List<Object[]> view(Long applyId,int pageNo, int pageSize){

		List<Financial> fins=getFins(applyId);
		StringBuilder sb = new StringBuilder(
				"SELECT BILLID AS BILLID,myshop._shop_mark AS shopMark,myshop._shuo_name AS shopName ,");
		if(fins!=null){
			for(Financial fin:fins)
				sb.append(" SUM(CASE WHEN BANK='").append(fin.getId()).append("' THEN _amount ELSE 0 END) AS '").append(fin.getName()).append("',");
		}
//		sb.append(" SUM(CASE WHEN BANK='1' THEN _amount ELSE 0 END) AS 中行,");
//		sb.append("	SUM(CASE WHEN BANK='4' THEN _amount ELSE 0 END) AS 工行,");
		sb.append("	COUNTS AS 总计 ,ALONEMONTH AS isAloneMonth,ALONE AS  isAlone");
		sb.append("	FROM (");
		sb.append("	SELECT bill._shop_id AS SHOP,bill.id AS BILLID,opt._financial_id AS BANK,opt._amount,bill._receivable_amount COUNTS,bill.aloneMonth as ALONEMONTH,bill.aloneOpen AS ALONE FROM mf_bill_ bill LEFT JOIN rs_mf_bill_option_ rs  ON bill.id=rs._bill_id  LEFT JOIN mf_bill_option_ opt ON opt.id=rs._option_id WHERE  ");
		sb.append("	 bill._bill_apply_id='").append(applyId).append("' ");
		sb.append("	) AS rus,sys_shop_ AS myshop WHERE rus.SHOP=myshop.id ");
		sb.append(" GROUP BY SHOP  ORDER BY myshop._shop_mark LIMIT "+((pageSize*pageNo)-pageSize)+", "+pageSize+";");
		

		SQLQuery sqlQuery = getSession().createSQLQuery(sb.toString());
//		sqlQuery.setFetchSize(3);
//		sqlQuery.setMaxResults(pageNo);
//		sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		 List<Object[]> list=sqlQuery.list();
		 /*for(Object[] objs:list){
			 for(Object obj:objs)
				 System.out.println(obj);
		 }*/
	
		return list;
	}
	
	//获得一个申请单中包含的金融机构
	public List<Financial> getFins(Long applyId){
		
		Criteria c=getSession().createCriteria(BillOption.class);
		Criteria bills=c.createCriteria("bills");
		bills.createCriteria("billApply").add(Restrictions.eq("id", applyId));
		c.setProjection(Projections.projectionList().add(Projections.groupProperty("financial")));
		return c.list();
	}
}
